﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing.Imaging;
using DataAccessLayer;
using ATM_Pro.Module;
using System.Data;

namespace ATM_Pro.Public
{
    public class QeuryInfoHandle
    {
        public QeuryInfoHandle()
        {
            helper = new AccessHelper(Properties.Settings.Default.AccessConnectiongString);
        }

        AccessHelper helper = null;

        #region WarnInfoHandle
        public WarnPacketBean Wpb { get; set; }
        /// <summary>
        /// 报警信息查询
        /// </summary>
        /// <param name="begin">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="type">报警类型</param>
        /// <param name="atmId">ATM编号</param>
        /// <param name="camId">摄像机编号</param>
        /// <param name="wtype">报警类型</param>
        /// <returns></returns>
        public DataSet Query(DateTime begin, DateTime end,
                              string atmName, string camName, string wtype,
                              int index, int pagecount, ref int count)
        {
            string tbName = "tb_WarnInfo";
            count = Convert.ToInt32(helper.ExecuteScalar(string.Format("select count(*) from {0} where (wType_Name='{1}') and (wTime > #{2}#) and (wTime < #{3}#) and (Atm_Address='{4}') and (Cam_Name='{5}') ",
                                          tbName, wtype, begin.ToString(), end.ToString(), atmName, camName)));
            if (count == 0)
                return null;
            return helper.QueryPaging(GenerateString(tbName, begin, end, atmName, camName, wtype, index, pagecount), tbName, ref count);
        }

        public string GenerateString(string tbName, DateTime begin, DateTime end,
                                    string atmName, string camName, string wtype,
                                    int index, int pagecount)
        {
            string sqlString = "select top {0} ID,wId,wType_Name,wLevel,wTime,wImgPath,Atm_Address,Cam_Name,TransImgPath from {1} ";
            if (index != 0)
            {
                sqlString = string.Format(sqlString + "where (wType_Name='{2}') and (wTime > #{3}#) and (wTime < #{4}#) and (Atm_Address='{5}') and (Cam_Name='{6}') and (ID not in (select top {7} ID from " + tbName + "))", pagecount, tbName, wtype, begin.ToString(), end.ToString(), atmName, camName, pagecount * index);
            }
            else
            {
                sqlString = string.Format(sqlString + "where (wType_Name='{2}') and (wTime > #{3}#) and (wTime < #{4}#) and (Atm_Address='{5}') and (Cam_Name='{6}')", pagecount, tbName, wtype, begin.ToString(), end.ToString(), atmName, camName);
            }
            return sqlString;
        }

        /// <summary>
        /// insert into db warninfo
        /// </summary>
        /// <returns></returns>
        public void InsertWarninfo()
        {
            string sqlStr = "insert into tb_WarnInfo(wId,wType_Id,wType_Name,wLevel,wTime,wImgPath,Atm_Id,Atm_Address,Cam_Id,Cam_Name,TransImgPath)";
            sqlStr += "values('" + Wpb.WarnId.ToString() + "'," + Wpb.WarnType + ",'" + Enum.GetName(typeof(WarnType), Wpb.WarnType) +
                "'," + Wpb.WarnLevel.ToString() + ",#" + Wpb.WarnTime.ToString() + "#,'" + Wpb.Path + "'," + Wpb.AtmBean.ID.ToString() + ",'" + Wpb.AtmBean.AddName + "'," + Wpb.AtmBean.Cam.ID.ToString() + ",'" + Wpb.AtmBean.Cam.Name + "','"+Wpb.TransPath+"')";
            helper.ExecuteNonQuery(sqlStr);
        }
        #endregion

        #region TransInfoHandle
        public TransPacketBean Tpb { get; set; }
        //查询交易信息的重载
        public DataSet Query(DateTime begin, DateTime end,
                              string atmName, string camName,
                              int index, int pagecount, ref int count)
        {
            string tbName = "tb_TransInfo";
            count = Convert.ToInt32(helper.ExecuteScalar(string.Format("select count(*) from {0} where (tTransTime > #{1}#) and (tTransTime < #{2}#) and (Atm_Address='{3}') and (Cam_Name='{4}')"
                                                 , tbName, begin.ToString(), end.ToString(), atmName, camName)));
            if (count == 0)
                return null;
            return helper.QueryPaging(GenerateString(tbName, begin, end, atmName, camName, index, pagecount), tbName, ref count);
        }

        public string GenerateString(string tbName, DateTime begin, DateTime end,
                                   string atmName, string camName,
                                   int index, int pagecount)
        {
            string sqlString = "select top {0} ID,tCardId,tTransTime,tFaceImgPath,tOrginImgPath,Atm_Address,Cam_Name from {1} ";
            if (index != 0)
            {
                sqlString = string.Format(sqlString + "where (tTransTime > #{2}#) and (tTransTime < #{3}#) and (Atm_Address='{4}') and (Cam_Name='{5}') and (ID not in (select top {6} ID from " + tbName + "))", pagecount, tbName, begin.ToString(), end.ToString(), atmName, camName, pagecount * index);
            }
            else
            {
                sqlString = string.Format(sqlString + "where (tTransTime > #{2}#) and (tTransTime < #{3}#) and (Atm_Address='{4}') and (Cam_Name='{5}')", pagecount, tbName, begin.ToString(), end.ToString(), atmName, camName);
            }

            return sqlString;
        }


        /// <summary>
        /// insert into db transinfo
        /// </summary>
        /// <returns></returns>
        public void InsertTransinfo(object obj)
        {
            string sqlStr = "insert into tb_TransInfo(tCardId,tTransTime,tFaceImgPath,tOrginImgPath,Atm_Id,Atm_Address,Cam_Id,Cam_Name)";
            sqlStr += " values('" + Tpb.CardId + "',#" + Tpb.TransTime.ToString() + "#,'" + Tpb.Path + "','" +
                Tpb.OriginImgPath + "'," + Tpb.AtmBean.ID.ToString() + ",'" + Tpb.AtmBean.AddName + "'," + Tpb.AtmBean.Cam.ID.ToString() + ",'" + Tpb.AtmBean.Cam.Name + "')";
            helper.ExecuteNonQuery(sqlStr);
        }

        public DataTable GetTransInfoByPath(string transPath)
        {
            string tbName = "tb_TransInfo";
            return helper.Query("select * from " + tbName + " where tOrginImgPath='" + transPath + "'");
        }
        #endregion
    }


}
